/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import static Database.Connect.ConnectMySQL;
import com.mysql.jdbc.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author Hai
 */
public class GetData {

    public GetData() {
    }

    public static int getTotalRows(String sTable) throws SQLException {
        String select = "SELECT [id] FROM " + sTable;
        int count = 0;
        try {
            ResultSet rs = execQuerySQL(select);
            while (rs.next()) {
                count++;
            }
        } catch (Exception e) {
        }

        return count;
    }

    public static String Table(String sTable) {
        String ret = "";
        int start = 1, end = start + 9;

        String sSQL = "SELECT * FROM `" + sTable + "` WHERE `id`>=" + start + " and `id`<=" + end + ";";

        try {
            ResultSet rs = execQuerySQL(sSQL);

            int count = 0;
            while (rs.next()) {
                if (count > 0) {
                    ret += "/n";
                }
                ret += (rs.getString("id") + "/r" + rs.getString("Title") + "/r" + rs.getString("IDNguoiPost") + "/r" + rs.getString("ThoiGianPost"));

                count++;
            }
        } catch (Exception e) {
        }

        return ret;
    }

    public static String Table(String sTable, int page) {
        String ret = "";
        int numRecord = 9;

        String sSQL = "SELECT * FROM " + sTable + " ORDER BY id ASC LIMIT " + ((page - 1) * 10) + "," + numRecord;

        try {
            ResultSet rs = execQuerySQL(sSQL);

            int count = 0;
            while (rs.next()) {
                if (count > 0) {
                    ret += "/n";
                }
                ret += (rs.getString("id") + "/r" + rs.getString("Title") + "/r" + rs.getString("IDNguoiPost") + "/r" + rs.getString("ThoiGianPost"));

                count++;
            }
        } catch (Exception e) {
        }

        return ret;
    }

    public static String getRowTable(String sTable, String id) {
        String ret = "";
        String sSQL = "SELECT * FROM `" + sTable + "` WHERE `id`=" + id + ";";

        try {
            Connection conn = ConnectMySQL();
            Statement st = (Statement) conn.createStatement();
            ResultSet rs = execQuerySQL(sSQL);

            while (rs.next()) {

                ret = (rs.getString("id")
                        + "/r" + rs.getString("Title")
                        + "/r" + rs.getString("content")
                        + "/r" + rs.getString("IDNguoiPost")
                        + "/r" + rs.getTimestamp("ThoiGianPost"));
            }

            conn.close();
        } catch (Exception e) {
        }

        return ret;
    }

    /**
     * Nhận id trả về nickname thành viên
     *
     * @param id
     * @return
     */
    public static String id2Name(String id) {
        String ret = null;
        String sSQL = "SELECT `idUser` FROM `user` WHERE id=" + id;

        try {
            ResultSet rs = execQuerySQL(sSQL);
            if (rs.next()) {
                ret = rs.getString("idUser");
            }

        } catch (Exception e) {
        }

        return ret;
    }

    public static ResultSet execQuerySQL(String sSQL) {

        Connection Conn = Database.Connect.ConnectMySQL();
        Statement SQLStatement;
        ResultSet ret = null;
        try {
            SQLStatement = (Statement) Conn.createStatement();
            ret = SQLStatement.executeQuery(sSQL);
        } catch (SQLException ex) {
        }

        return ret;
    }
//================

    public static int numRow(String sTable) throws SQLException {
        String Query = "SELECT count(*) FROM " + sTable;

        ResultSet row = execQuerySQL(Query);
        // Code tạo phân trang
        int numRow = 0;

        if (row.next()) {
            numRow = row.getInt(1);
        }
        return numRow;
    }

    public static String numPage(String sTable, int page) {
        String ret = "";

        try {
            int numRow = numRow(sTable);

            int numRecord = 10;

            int n = numRow;
            int j = 1;
            int m = numRecord;

            for (int i = 0; i < n; i++) {
                if (i % m == 0) {
                    if (page >= 0) {
                        if (j == page) {
                            ret += ("<span>" + j + "</span>");
                        } else if (page + 1 == i) {
                            ret += ("<a href='?where=" + sTable + "&p=" + j + "'><b>" + j + "</b></a> ");
                        } else {
                            ret += ("<a href='?where=" + sTable + "&p=" + j + "'>" + j + "</a> ");
                        }
                    }

                    j++;
                }
            }
        } catch (SQLException ex) {
        }
        return ret;
    }

}
